/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package manageroute;

import Database.ConnectDB;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author norbert
 */
public class DBPkt {

    //static ConnectDB connectDB;
    String idTrayek;
    private String kotaAsal;
    String kotaTujuan;
    int tarif;

    public String getIdTrayek() {
        return idTrayek;
    }

    public void setIdTrayek(String idTrayek) {
        this.idTrayek = idTrayek;
    }

    public String getKotaAsal() {
        return kotaAsal;
    }

    public void setKotaAsal(String kotaAsal) {
        this.kotaAsal = kotaAsal;
    }

    public String getKotaTujuan() {
        return kotaTujuan;
    }

    public void setKotaTujuan(String kotaTujuan) {
        this.kotaTujuan = kotaTujuan;
    }

    public int getTarif() {
        return tarif;
    }

    public void setTarif(int tarif) {
        this.tarif = tarif;
    }

    public DBPkt(String idTrayek, String kotaAsal, String kotaTujuan, int tarif)
    {
        this.idTrayek = idTrayek;
        this.kotaAsal = kotaAsal;
        this.kotaTujuan = kotaTujuan;
        this.tarif = tarif;
    }

    public DBPkt(String kotaAsal, String kotaTujuan, int tarif)
    {
        this.kotaAsal = kotaAsal;
        this.kotaTujuan = kotaTujuan;
        this.tarif = tarif;
    }

    @SuppressWarnings("static-access")
    static boolean CheckAlreadyExists(String kotaAsal, String kotaTujuan)
    {
        boolean found = false;
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "select * from trayek_pkt where kota_asal='"+kotaAsal+"' and kota_tujuan='"+kotaTujuan+"'";
            connectDB.result = connectDB.stmt.executeQuery(query);

            if (connectDB.result.next())
            {
                found = true;
            }
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
        }
        return found;
    }

    @SuppressWarnings("static-access")
    static boolean CheckUpdating(String kotaAsal, String kotaTujuan, String idTrayek)
    {
        boolean found = false;
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "select * from trayek_pkt where kota_asal='"+kotaAsal+"' and kota_tujuan='"+kotaTujuan+"'";
            connectDB.result = connectDB.stmt.executeQuery(query);

            while (connectDB.result.next())
            {
                if (!connectDB.result.getString("id_trayek_pkt").equals(idTrayek))
                {
                    found = true;
                    break;
                }
            }
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
        }
        return found;
    }

    @SuppressWarnings("static-access")
    public static String insertDBPkt(String kotaAsal, String kotaTujuan, String tarif)
    {
        if (CheckAlreadyExists(kotaAsal, kotaTujuan))
        {
            String error = "Rute tersebut telah ada.";
            return error;
        }
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "insert into trayek_pkt (kota_asal,kota_tujuan,tarif) values ('" + kotaAsal + "','" + kotaTujuan + "','" + tarif + "')";
            connectDB.stmt.executeUpdate(query);
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
            String error = "Gagal menyimpan data. Tarif harus berisi angka.";
            ConnectDB.Disconnect();
            return error;
        }
        ConnectDB.Disconnect();
        return null;
    }

    @SuppressWarnings("static-access")
    public static String insertDBPkt(DBPkt newDBPkt)
    {
        if (CheckAlreadyExists(newDBPkt.kotaAsal, newDBPkt.kotaTujuan))
        {
            String error = "Rute tersebut telah ada.";
            return error;
        }
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "insert into trayek_pkt (kota_asal,kota_tujuan,tarif) values ('" + newDBPkt.kotaAsal + "','" + newDBPkt.kotaTujuan + "','" + newDBPkt.tarif + "')";
            connectDB.stmt.executeUpdate(query);
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
            String error = "Gagal menyimpan data. Tarif harus berisi angka.";
            ConnectDB.Disconnect();
            return error;
        }
        ConnectDB.Disconnect();
        return null;
    }

    @SuppressWarnings("static-access")
    public static String updateDBPkt(String idTrayek, String kotaAsal, String kotaTujuan, String tarif)
    {
        if (CheckUpdating(kotaAsal, kotaTujuan, idTrayek))
         {
             String error = "Rute tersebut telah ada.";
             return error;
         }
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "update trayek_pkt set kota_asal='" + kotaAsal + "', kota_tujuan='"+kotaTujuan+"', tarif='"+tarif+"' where id_trayek_pkt='"+idTrayek+"'";
            connectDB.stmt.executeUpdate(query);
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
            String error = "Gagal update data. Tarif harus berisi angka.";
            ConnectDB.Disconnect();
            return error;
        }
        ConnectDB.Disconnect();
        return null;
    }

    @SuppressWarnings("static-access")
    public static String updateDBPkt(DBPkt newDBPkt)
    {
        if (CheckUpdating(newDBPkt.kotaAsal, newDBPkt.kotaTujuan, newDBPkt.idTrayek))
         {
             String error = "Rute tersebut telah ada.";
             return error;
         }
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "update trayek_pkt set kota_asal='" + newDBPkt.kotaAsal + "', kota_tujuan='"+ newDBPkt.kotaTujuan +"', tarif='"+newDBPkt.tarif+"' where id_trayek_pkt='"+newDBPkt.idTrayek+"'";
            connectDB.stmt.executeUpdate(query);
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
            String error = "Gagal update data. Tarif harus berisi angka.";
            ConnectDB.Disconnect();
            return error;
        }
        ConnectDB.Disconnect();
        return null;
    }

    @SuppressWarnings("static-access")
    public static String deleteDBPkt(String idTrayek)
    {
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "delete from trayek_pkt where id_trayek_pkt='"+idTrayek+"'";
            connectDB.stmt.executeUpdate(query);
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
            String error = "Gagal menghapus data.";
            ConnectDB.Disconnect();
            return error;
        }
        ConnectDB.Disconnect();
        return null;
    }



    @SuppressWarnings("static-access")
    public static ArrayList<DBPkt> getAllRows()
    {
        ArrayList<DBPkt> dbpktList = new ArrayList<DBPkt>();

        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "select * from trayek_pkt";
            connectDB.result = connectDB.stmt.executeQuery(query);

            while (connectDB.result.next())
            {
                String idTrayek = connectDB.result.getString("id_trayek_pkt");
                String kotaAsal = connectDB.result.getString("kota_asal");
                String kotaTujuan = connectDB.result.getString("kota_tujuan");
                int tarif = connectDB.result.getInt("tarif");

                DBPkt curAj = new DBPkt(idTrayek, kotaAsal, kotaTujuan, tarif);
                dbpktList.add(curAj);
            }
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
        }
        return dbpktList;
    }

    @SuppressWarnings("static-access")
    public static DBPkt getDBPkt(String idTrayek)
    {
        try {
            ConnectDB connectDB = new ConnectDB("localhost:3306", "PPL", "cinta", "cinta");
            connectDB.stmt = connectDB.conn.createStatement();
            String query = "select * from trayek_pkt where id_trayek_pkt='"+idTrayek+"'";
            connectDB.result = connectDB.stmt.executeQuery(query);

            if (connectDB.result.next())
            {
                //String idTrayek = connectDB.result.getString("id_trayek_pkt");
                String kotaAsal = connectDB.result.getString("kota_asal");
                String kotaTujuan = connectDB.result.getString("kota_tujuan");
                int tarif = connectDB.result.getInt("tarif");

                DBPkt curAj = new DBPkt(idTrayek, kotaAsal, kotaTujuan, tarif);
                return curAj;
            }
            connectDB.Disconnect();
        }
        catch (SQLException e) {
            System.out.println("Error occurred " + e);
        }

        return null;
    }


}
